CREATE PROCEDURE [dbo].[PDA_CalcOverUnderRow]
    @Id VARCHAR(MAX),
    @AutoId VARCHAR(MAX),
    @isUpdate BIT = 0
AS
--DECLARE @Id varchar(MAX) = 12035
--DECLARE @AutoId VARCHAR(MAX) = 23274
--DECLARE @isUpdate BIT = 0
IF (@isUpdate = 0)
BEGIN

    DELETE FROM Tmp_Over_Under_Row
    WHERE AutoId = @AutoId;

    INSERT INTO Tmp_Over_Under_Row
    (
        AutoId,
        Name,
        Type,
        Code_Over_Under,
        Max_Percent,
        Max_Price,
        ShowPrice,
        Price_Row,
        Price,
        [Percent]
    )
    SELECT TmpH.AutoId AS AutoId_Tmph,
           OU.Name,
           OU.Type,
           OU.AutoId AS AutoId_OU,
           CASE
               WHEN TaxPercent <> 0
                    AND ISNULL(OU.Formula, '') = 1
                    AND OU.Type = 1 THEN
                   TaxPercent
               WHEN DiscountPercent <> 0
                    AND ISNULL(OU.Formula, '') = 1
                    AND OU.Type = 0 THEN
                   DiscountPercent
               ELSE
                   OU.Max_Percent
           END Max_Percent,
           OU.Max_Price,
           OU.ShowPrice,
           CASE
               WHEN ISNULL(OU.Formula, '') = 1 THEN
                   1
               ELSE
                   0
           END AS Formula,
           ROUND(   TmpH.Tot_Forosh * .01 * (CASE
                                                 WHEN TaxPercent <> 0
                                                      AND ISNULL(OU.Formula, '') = 1
                                                      AND OU.Type = 1 THEN
                                                     TaxPercent
                                                 WHEN DiscountPercent <> 0
                                                      AND ISNULL(OU.Formula, '') = 1
                                                      AND OU.Type = 0 THEN
                    (DiscountPercent)
                                                 ELSE
                                                     OU.Max_Percent
                                             END
                                            ),
                    0
                ) price,
           CASE
               WHEN TaxPercent <> 0
                    AND ISNULL(OU.Formula, '') = 1
                    AND OU.Type = 1 THEN
                   TaxPercent
               WHEN DiscountPercent <> 0
                    AND ISNULL(OU.Formula, '') = 1
                    AND OU.Type = 0 THEN
                   DiscountPercent
               ELSE
                   OU.Max_Percent
           END [PERCENT]
    FROM dbo.Over_Under OU WITH (NOLOCK)
        CROSS JOIN dbo.TmpHavaleh TmpH WITH (NOLOCK)
        INNER JOIN dbo.KalaId G WITH (NOLOCK)
            ON G.K_Code = TmpH.K_Code
    WHERE TmpH.AutoId = @AutoId
          AND OU.Active = 1
          AND OU.Action_Code = 7
          AND OU.AzTarikh <= CAST(GETDATE() AS DATE)
          AND OU.TaTarikh >= CAST(GETDATE() AS DATE)
          AND G.Duty_Flag = 1
          AND NOT EXISTS
    (
        SELECT *
        FROM Tmp_Over_Under_Row WITH (NOLOCK)
        WHERE AutoId = TmpH.AutoId
              AND Code_Over_Under = OU.AutoId
    )
          AND OU.ShowPrice = 1
    ORDER BY AutoId_Tmph,
             OU.SortDisplay;
    EXEC dbo.Sp_Duty_Row @Id, @AutoId, 7;
END;

ELSE
BEGIN
    UPDATE dbo.Tmp_Over_Under_Row
    SET Price = TmpH.Tot_Forosh * .01 * (CASE
                                             WHEN TaxPercent <> 0
                                                  AND ISNULL(OU.Formula, '') = 1
                                                  AND OU.Type = 1 THEN
                                                 TaxPercent
                                             WHEN DiscountPercent <> 0
                                                  AND ISNULL(OU.Formula, '') = 1
                                                  AND OU.Type = 0 THEN
                                                 DiscountPercent
                                             ELSE
                                                 OU.Max_Percent
                                         END
                                        ),
        [Percent] = CASE
                        WHEN TaxPercent <> 0
                             AND ISNULL(OU.Formula, '') = 1
                             AND OU.Type = 1 THEN
                            TaxPercent
                        WHEN DiscountPercent <> 0
                             AND ISNULL(OU.Formula, '') = 1
                             AND OU.Type = 0 THEN
                            DiscountPercent
                        ELSE
                            OU.Max_Percent
                    END
    FROM dbo.Tmp_Over_Under_Row TOUR
        JOIN dbo.Over_Under OU WITH (NOLOCK)
            ON OU.AutoId = TOUR.Code_Over_Under
        JOIN dbo.TmpHavaleh TmpH WITH (NOLOCK)
            ON TmpH.AutoId = TOUR.AutoId
        INNER JOIN dbo.KalaId WITH (NOLOCK)
            ON KalaId.K_Code = TmpH.K_Code
    WHERE TOUR.AutoId = @AutoId
          AND OU.Active = 1
          AND OU.Action_Code = 7
          AND OU.AzTarikh <= CAST(GETDATE() AS DATE)
          AND OU.TaTarikh >= CAST(GETDATE() AS DATE)
          AND OU.ShowPrice = 1
          AND dbo.KalaId.Duty_Flag = 1;
    EXEC dbo.Sp_Duty_Row @Id, @AutoId, 7;


END;

